package com.xz.group12306.business.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.xz.group12306.business.common.DBUtils;
import com.xz.group12306.business.dao.ManagerDao;
import com.xz.group12306.domain.Ticket;
import com.xz.group12306.domain.User;

/**
 * @author 张爱文
 *
 */

public class ManagerDaoImpl implements ManagerDao{

	/**
	 * 数据库连接
	 */
	private Connection conn;

	/**
	 * 构造方法
	 */
	public ManagerDaoImpl(Connection conn) {
		this.conn = conn;
	}

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}
	/**
	 * 根据用户信息查询用户 张爱文
	 */
	@Override
	public User findUser(User one) throws SQLException {
		// TODO Auto-generated method stub
		StringBuffer find_sql = new StringBuffer();
		find_sql.append("select * from tab_user");
		find_sql.append(" where 1=1");

		// 查询条件标记
		boolean tag = false;
		// 查询条件id字段
		Integer id = one.getId();
		if (id != null && id != 0) {
			find_sql.append(" AND id=" + id);
			tag = true;
		}
		// 查询条件username字段
		String username = one.getUsername();
		if (username != null && !username.isEmpty()) {
			find_sql.append(" AND username='" + username + "'");
			tag = true;
		}
		// 查询条件password字段
		String password = one.getPassword();
		if (password != null && !password.isEmpty()) {
			find_sql.append(" AND password='" + password + "'");
			tag = true;
		}
		// 查询条件rule字段
		String rule = one.getRule();
		if (rule != null && !rule.isEmpty()) {
			find_sql.append(" AND rule='" + rule + "'");
			tag = true;
		}
		// 查询条件realname字段，模糊查询
		String realname = one.getRealname();
		if (realname != null && !realname.isEmpty()) {
			find_sql.append(" AND realname LIKE '%" + realname + "%'");
			tag = true;
		}
		// 查询条件sex字段
		String sex = one.getSex();
		if (sex != null && !sex.isEmpty()) {
			find_sql.append(" AND sex='" + sex + "'");
			tag = true;
		}
		// 查询条件city字段
		String city = one.getCity();
			if (city != null && city.isEmpty()) {
				find_sql.append(" AND city=" + city);
				tag = true;
			}
		
		// 查询条件cert_type字段--有错
		// if (one.getCertType() != null) {
		// Integer certtype = one.getCertType().getId();
		// if (certtype != null && certtype != 0) {
		// find_sql.append(" AND cert_type=" + certtype);
		// tag = true;
		// }
		// }
		// 查询条件cert字段
		String cert = one.getCert();
		if (cert != null && !cert.isEmpty()) {
			find_sql.append(" AND cert LIKE '%" + cert + "%'");
			tag = true;
		}
		// 查询条件user_type字段
			String usertype = one.getUserType();
			if (usertype != null && usertype.isEmpty()) {
				find_sql.append(" AND usertype=" + usertype);
				tag = true;
			}
		// 查询条件content字段
		String content = one.getContent();
		if (content != null && !content.isEmpty()) {
			find_sql.append(" AND content LIKE '%" + content + "%'");
			tag = true;
		}
		// 查询条件status字段
		String status = one.getStatus();
		if (status != null && !status.isEmpty()) {
			find_sql.append(" AND status='" + status + "'");
			tag = true;
		}

		// 查询条件image_path字段
		String image = one.getImagePath();
		if (image != null && !image.isEmpty()) {
			find_sql.append(" AND image_path='" + image + "'");
			tag = true;
		}

		System.out.println(find_sql.toString());

		// 若没有查询条件则返回对象为null
		if (!tag) {
			return null;
		}

		User user = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = conn.prepareStatement(find_sql.toString());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				// 解析结果集对象，封装查询结果
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setRule(rs.getString("rule"));
				user.setRealname(rs.getString("realname"));
				user.setSex(rs.getString("sex"));
				user.setCert(rs.getString("cert"));

				// city
				/*
				 * Province province = new Province(); province.setId(rs.getInt("pid"));
				 * province.setProvince(rs.getString("pprovince"));
				 * province.setProvinceId(rs.getString("pprovinceid"));
				 * 
				 * City city = new City(); city.setId(rs.getInt("cid"));
				 * city.setCityId(rs.getString("ccityid")); city.setCity(rs.getString("ccity"));
				 * city.setProvince(province);
				 * 
				 * user.setCity(city);
				 * 
				 * // CertType CertType certType = new CertType();
				 * certType.setId(rs.getInt("eid"));
				 * certType.setContent(rs.getString("econtent")); user.setCertType(certType);
				 * 
				 * user.setCert(rs.getString("cert")); user.setBirthday(rs.getDate("birthday"));
				 * 
				 * // UserType UserType userType = new UserType();
				 * userType.setId(rs.getInt("tid"));
				 * userType.setContent(rs.getString("tcontent")); user.setUserType(userType);
				 * 
				 * user.setContent(rs.getString("content"));
				 * user.setStatus(rs.getString("status"));
				 * user.setLoginIp(rs.getString("login_ip"));
				 * user.setImagePath(rs.getString("image_path"));
				 */
			}
		} finally {
			DBUtils.closeStatement(rs, pstmt);
		}
		return user;
	}

	/**
	 * 这个可以用于信息的修改
	 */
	@Override
	public boolean updateUser(User one) throws SQLException {
		// TODO Auto-generated method stub
		// SQL语句
				StringBuffer update_sql = new StringBuffer("UPDATE tab_user set ");
				// 查询条件标记
				boolean tag = false;
				// 查询条件id字段
				Integer id = one.getId();// null
				// if (id == null || id == 0) {
				// return false;
				// } else {
				// update_sql.append(" id=" + id);
				// }
				// 查询条件username字段
				String username = one.getUsername();
				if (username != null && !username.isEmpty()) {
					update_sql.append(" username='" + username + "'");
					tag = true;
				}
				// 查询条件password字段
				// String password = one.getPassword();
				// if (password != null && !password.isEmpty()) {
				// update_sql.append(", password='" + password + "'");
				// tag = true;
				// }
				// 查询条件rule字段
				// String rule = one.getRule();
				// if (rule != null && !rule.isEmpty()) {
				// update_sql.append(", rule='" + rule + "'");
				// tag = true;
				// }
				// 查询条件realname字段，模糊查询
				// String realname = one.getRealname();
				// if (realname != null && !realname.isEmpty()) {
				// update_sql.append(", realname='" + realname + "'");
				// tag = true;
				// }
				// 查询条件sex字段
				// String sex = one.getSex();
				// if (sex != null && !sex.isEmpty()) {
				// update_sql.append(", sex='" + sex + "'");
				// tag = true;
				// }
				// 查询条件city字段
				// if (one.getCity() != null) {
				// Integer city = one.getCity().getId();
				// if (city != null && city != 0) {
				// update_sql.append(", city=" + city);
				// tag = true;
				// }
				// }
				// 查询条件cert_type字段--有错
				// if (one.getCertType() != null) {
				// Integer certtype = one.getCertType().getId();
				// if (certtype != null && certtype != 0) {
				// update_sql.append(", cert_type=" + certtype);
				// tag = true;
				// }
				// }
				// 查询条件cert字段
				String cert = one.getCert();
				if (cert != null && !cert.isEmpty()) {
					update_sql.append(", cert='" + cert + "'");
					tag = true;
				}
				// 查询条件user_type字段
				// if (one.getUserType() != null) {
				// Integer usertype = one.getUserType().getId();
				// if (usertype != null && usertype != 0) {
				// update_sql.append(", user_type=" + usertype);
				// tag = true;
				// }
				// }
				// 查询条件content字段
				// String content = one.getContent();
				// if (content != null && !content.isEmpty()) {
				// update_sql.append(", content='" + content + "'");
				// tag = true;
				// }
				// 查询条件status字段
				// String status = one.getStatus();
				// if (status != null && !status.isEmpty()) {
				// update_sql.append(", status='" + status + "'");
				// tag = true;
				// }
				// 查询条件login_ip字段
				// String ip = one.getLoginIp();
				// if (ip != null && !ip.isEmpty()) {
				// update_sql.append(", login_ip='" + ip + "'");
				// tag = true;
				// }
				// 查询条件image_path字段
				// String image = one.getImagePath();
				// if (image != null && !image.isEmpty()) {
				// update_sql.append(", image_path='" + image + "'");
				// tag = true;
				// }
				// + id
				update_sql.append(" where id =" + id);

				System.out.println(update_sql.toString());
				// 若没有查询条件则返回对象为null
				if (!tag) {
					return false;
				}

				int idx = 2;
				int row = 0;
				PreparedStatement pstmt = null;

				try {
					// 设置语句对象，SQL语句条件
					pstmt = conn.prepareStatement(update_sql.toString());
					//
					// pstmt.setString(idx, one.getUsername());
					// pstmt.setString(++idx, one.getPassword());
					// pstmt.setString(++idx, one.getRule());
					// pstmt.setString(++idx, one.getRealname());
					// pstmt.setString(++idx, one.getSex());
					// pstmt.setInt(++idx, one.getCity().getId());
					// pstmt.setString(++idx, one.getCertType());
					// pstmt.setInt(++idx, 1999);
					// pstmt.setString(++idx, one.getCert());
					// pstmt.setDate(++idx, new Date(one.getBirthday().getTime()));
					// pstmt.setInt(++idx, one.getUserType().getId());
					// pstmt.setString(++idx, one.getContent());
					// pstmt.setString(++idx, one.getStatus());
					// pstmt.setString(++idx, one.getLoginIp());
					// pstmt.setString(++idx, one.getImagePath());

					row = pstmt.executeUpdate();
					if (row != 0) {
						tag = true;
					} else {
						tag = false;
					}
				} catch (Exception e) {
					System.out.println("更新语句的下面有问题");
				} finally {
					DBUtils.closeStatement(null, pstmt);
				}
				return tag;
			}

	/**
	 * 修改更新数据用到的方法 张爱文
	 */
	@Override
	public List<User> findUserLists(User one) throws SQLException {
		// TODO Auto-generated method stub
		List<User> users = new ArrayList<User>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * ");
		sql.append("from tab_user ");
		sql.append("where 1=1");

		// 查询条件标记
		boolean tag = false;

		// 根据用户ID查
		Integer id = one.getId();
		if (id != null) {
			sql.append(" AND id="+id);
			tag = true;
		}

		// 根据用户名查
		String userName = one.getUsername();
		if (userName != null && !userName.isEmpty()) {
			sql.append(" AND username='" + userName + "'");
			tag = true;
		}

		// 查询条件sex字段
		String sex = one.getSex();
		if (sex != null && !sex.isEmpty()) {
			sql.append(" AND sex='" + sex + "'");
			tag = true;
		}

		// 查询条件cert字段
		String cert = one.getCert();
		if (cert != null && !cert.isEmpty()) {
			sql.append(" AND cert LIKE '%" + cert + "%'");
			tag = true;
		}

		System.out.println(sql.toString());
		// 若没有查询条件则返回对象为null
		if (!tag) {
			return null;
		}

		User user = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = conn.prepareStatement(sql.toString());

			rs = pstmt.executeQuery();
			while (rs.next()) {
				// 解析结果集对象，封装查询结果
				// 解析结果集对象，封装查询结果
				user = new User();

				user.setUsername(rs.getString("UserName"));
				user.setPassword(rs.getString("password"));
				user.setRule(rs.getString("rule"));
				user.setRealname(rs.getString("realname"));
				user.setSex(rs.getString("sex"));
				user.setCity(rs.getString("city"));

				// user.setCertType(Integer.getInteger(rs.getString("certType")));
				user.setCert(rs.getString("cert"));

				users.add(user);
				System.out.println("顶层");
			}
		}catch (Exception e) {
		System.out.println("chenggong");
		} 
		
		finally {
			DBUtils.closeStatement(rs, pstmt);
		}

		return users;

	}

	/**
	 * 查询所有用户的信息 张爱文
	 */
	@Override
	public List<User> findAll() {
		// TODO Auto-generated method stub
		// int start = (page - 1) * rows;
				List<User> list = new ArrayList<User>();
				StringBuffer sbsql = new StringBuffer();
				sbsql.append("select * from tab_user");
				// sbsql.append(" where ");
				// sbsql.append("limit ");
				// sbsql.append(+start);
				// sbsql.append("," + rows);
				System.out.println(sbsql.toString());
				try {
					PreparedStatement pstmt = null;
					pstmt = conn.prepareStatement(sbsql.toString());
					ResultSet rs = null;
					rs = pstmt.executeQuery();
					while (rs.next()) {
						User user = new User();
						user.setId(rs.getInt(1));
						user.setUsername((rs.getString(2)));
						//user.setPassword((rs.getString(3)));
						//user.setRule(rs.getString(4));
						//user.setRealname(rs.getString(5));
						user.setSex(rs.getString(6));
						// user.setCity(rs.getString(7).);

						user.setCertType(rs.getString(8));
						user.setCert(rs.getString(9));
						// user.setBirthday(rs.getString(10));
						// user.setUserType(rs.getString(11));
						//user.setContent(rs.getString(12));
						//user.setStatus(rs.getString(13));
						//user.setLoginIp(rs.getString(14));
						//user.setImagePath(rs.getString(15));

						list.add(user);
					}
				} catch (SQLException e) {
					e.printStackTrace();
					throw new RuntimeException(e);
				}
				return list;
			}

	
	/**
	 * 顾赛男 删除
	 */
	@Override
	public void delUser(int UserID) {
		// TODO Auto-generated method stub
		Connection conn = DBUtils.getConnection();
		String sql = "delete from tab_user where ID='" + UserID + "'";
		try {
			PreparedStatement pstmt = null;
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			DBUtils.closeConnection(conn);
			System.out.println("删除成功");
			System.out.println(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	
	/**
	 * 添加用户信息 顾赛男
	 */
	@Override
	public void addUser(User user) throws SQLException {
		// TODO Auto-generated method stub
		Connection conn = DBUtils.getConnection();
		String sql = "insert into tab_user(username,password,rule,realname,sex,city,cert_type,cert) values(?,?,?,?,?,?,?,?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, user.getUsername());
		ps.setString(2, user.getPassword());
		ps.setString(3, user.getRule());
		ps.setString(4, user.getRealname());
		ps.setString(5, user.getSex());
		ps.setString(6, user.getCity());
		ps.setString(7, user.getCertType());
		
		ps.setString(8, user.getCert());
    
		ps.executeUpdate();
		System.out.println(sql);
		DBUtils.closeConnection(conn);

	}

	/**
	 * 顾赛男 根据用户信息查询用户记录
	 */
	@Override
	public List<User> findUserList(User one, int page, int rows) throws SQLException {
		// TODO Auto-generated method stub
		int start = (page - 1) * rows;
		List<User> users = new ArrayList<User>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * ");
		sql.append("from tab_user ");
		sql.append("where ");

		// 查询条件标记
		boolean tag = false;

		// 根据用户名查
		String userName = one.getUsername();
		if (userName != null && !userName.isEmpty()) {
			sql.append(" username='" + userName + "'");
			tag = true;
		}

		// 查询条件sex字段
		String sex = one.getSex();
		if (sex != null && !sex.isEmpty()) {
			sql.append("  sex='" + sex + "'");
			tag = true;
		}

		// 查询条件cert字段
		String cert = one.getCert();
		if (cert != null && !cert.isEmpty()) {
			sql.append(" cert LIKE '%" + cert + "%'");
			tag = true;
		}

		sql.append("  limit ");
		sql.append(+start);
		sql.append("," + rows);
		System.out.println(sql.toString());
		// 若没有查询条件则返回对象为null
		if (!tag) {
			return null;
		}

		User user = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = conn.prepareStatement(sql.toString());

			rs = pstmt.executeQuery();
			while (rs.next()) {
				// 解析结果集对象，封装查询结果
				// 解析结果集对象，封装查询结果
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("UserName"));
				user.setPassword(rs.getString("password"));
				user.setRule(rs.getString("rule"));
				user.setRealname(rs.getString("realname"));
				user.setSex(rs.getString("sex"));

				user.setCity(rs.getString("city"));


				// user.setCertType(Integer.getInteger(rs.getString("certType")));
				user.setCertType(rs.getString("cert_type"));
				user.setCert(rs.getString("cert"));

				users.add(user);
			}
		} finally {
			DBUtils.closeStatement(rs, pstmt);
		}
		return users;
	}

	// start是页码，pageSize是每页的条数
	@Override
	public List<User> getUserList(int pageSize, int start, User one) throws SQLException {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		sql.append("select * ");
		sql.append("from tab_user");
		sql.append("where ");

		// 查询条件userName字段
		String userName = one.getUsername();
		if (userName != null && !userName.isEmpty()) {
			sql.append("  username='" + userName + "'");
		}
		// 查询条件sex字段
		String sex = one.getSex();
		if (sex != null && !sex.isEmpty()) {
			sql.append("  sex='" + sex + "'");
		}

		// 分页SQL语句
		String Page_sql = "select t.* from  (" + sql.toString() + ") t pageSize(start-1)*pageSize,pageSize";
		User user = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<User> list = new ArrayList<User>();
		try {
			pstmt = conn.prepareStatement(Page_sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				// 解析结果集对象，封装查询结果
				user = new User();
				user.setId(rs.getInt("UserID"));
				user.setUsername(rs.getString("UserName"));
				user.setPassword(rs.getString("password"));
				user.setRule(rs.getString("rule"));
				user.setRealname(rs.getString("realname"));
				user.setSex(rs.getString("sex"));
				user.setCity(rs.getString("city"));
				// CertType
				user.setCertType(rs.getString("cert_type"));

				user.setCert(rs.getString("cert"));

				list.add(user);
			}
		} finally {
			DBUtils.closeStatement(rs, pstmt);
		}

		return list;
	}

	/**
	 * 增加车票信息 张爱文
	 */
	@Override
	public void addTicket(Ticket ticket) throws SQLException {
		// TODO Auto-generated method stub
		Connection conn = DBUtils.getConnection();
		String sql = "insert into tab_ticket(depature,destinction,trainno,seatno,costtime,ticketno) values(?,?,?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, ticket.getDepature());
		ps.setString(2, ticket.getDestinction());
		ps.setString(3, ticket.getTrainNo());
		//ps.setDate(4, new java.sql.Date(ticket.getStartTime().getTime()));
		ps.setInt(4, ticket.getSeatNo());
		ps.setString(5, ticket.getCostTime());
		ps.setInt(6, ticket.getTicketNo());
	

		ps.executeUpdate();
		DBUtils.closeConnection(conn);
		
		System.out.println(sql);
	}


	/**
	 * 查询所有车票的信息，用于增加车票信息增加后  张爱文
	 */
	@Override
	public List<Ticket> findAllTicket() throws SQLException {
		// TODO Auto-generated method stub
		List<Ticket> list = new ArrayList<Ticket>();
		StringBuffer sbsql = new StringBuffer();
		sbsql.append("select * from tab_ticket");
		
		System.out.println(sbsql.toString());
		try {
			PreparedStatement pstmt = null;
			pstmt = conn.prepareStatement(sbsql.toString());
			ResultSet rs = null;
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Ticket ticket = new Ticket();
				ticket.setTicketId(rs.getInt(1));
				ticket.setDepature(rs.getString(2));
				ticket.setDestinction(rs.getString(3));
				ticket.setTrainNo(rs.getString(4));
				ticket.setStartTime(rs.getDate(5));
				//ticket.setSeatNo(rs.getInt(6));
				ticket.setCostTime(rs.getString(7));
				ticket.setTicketNo(rs.getInt(8));

				list.add(ticket);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return list;
}

	
	/**
	 * 根据车票信息查新车票记录
	 */
	@Override
	public List<Ticket> findTicketList(Ticket ticket, int page, int rows) throws SQLException {
		// TODO Auto-generated method stub
		int start=(page-1)*rows;
		List<Ticket> tickets=new ArrayList<Ticket>();
		String depature=ticket.getDepature();
		String destinction=ticket.getDestinction();
		StringBuffer sql=new StringBuffer();
		sql.append("select * ");
		sql.append("from tab_ticket ");
		sql.append("where  depature='"+depature+"' and destinction='"+destinction+"'");
		
				sql.append("  limit ");
				sql.append(+start);
				sql.append(","+rows);
	        	System.out.println(sql.toString());
		
				Ticket ticket1=null;
				PreparedStatement pstmt = null;
				ResultSet rs = null;
			
				try {
				pstmt=conn.prepareStatement(sql.toString());
				
				rs = pstmt.executeQuery();
				while(rs.next()) {
					// 解析结果集对象，封装查询结果
					//解析结果集对象，封装查询结果
					ticket1=new Ticket();
					
					ticket1.setTicketId(rs.getInt(1));
					ticket1.setDepature(rs.getString(2));
					ticket1.setDestinction(rs.getString(3));
					ticket1.setTrainNo(rs.getString(4));
					ticket1.setStartTime(rs.getDate(5));
					//ticket1.setSeatNo(rs.getInt(6));
					ticket1.setCostTime(rs.getString(7));
					ticket1.setTicketNo(rs.getInt("ticketno"));
				
				tickets.add(ticket1);
				}
				} finally {
					DBUtils.closeStatement(rs, pstmt);
				}
		      return tickets;
	}
	


	/**
	 * 删除车票  顾赛男
	 */
	@Override
	public void delTicket(int TicketID) {
		// TODO Auto-generated method stub
		Connection conn=DBUtils.getConnection();
		String sql="delete from tab_ticket where TicketId='"+TicketID+"'";
		System.out.println(sql);
		try {
			PreparedStatement pstmt = null;
			pstmt=conn.prepareStatement(sql);
			pstmt.executeUpdate();
			DBUtils.closeConnection(conn);
			System.out.println("删除成功");
			System.out.println(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 根据车票id查询车票信息  顾赛男
	 */
	@Override
	public Ticket findTicket(int TicketID) throws SQLException {
		// TODO Auto-generated method stub
		String sql="select * from tab_ticket where TicketID='"+TicketID+"'";
		System.out.println(sql);
		//若没有查询条件则返回对象为null
				Ticket ticket= null;
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				try {
				pstmt=conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if (rs.next()) {
					// 解析结果集对象，封装查询结果
					//解析结果集对象，封装查询结果
					ticket=new Ticket();
				    ticket.setTicketId(rs.getInt("TicketId"));
					ticket.setDepature(rs.getString("Depature"));
					ticket.setDestinction(rs.getString("destinction"));
					ticket.setTrainNo(rs.getString("trainNo"));
					ticket.setStartTime(rs.getDate("startTime"));
					//ticket.setSeatNo(rs.getInt("seatno"));
					ticket.setCostTime(rs.getString("costtime"));
					ticket.setTicketNo(rs.getInt("ticketno"));
				}
				} finally {
					DBUtils.closeStatement(rs, pstmt);
				}
				return ticket;	
	}

	/**
	 * 修改车票信息
	 */
	@Override
	public void updateTicket(Ticket ticket) {
		// TODO Auto-generated method stub
		Connection conn=DBUtils.getConnection();
		String sql="update tab_ticket set depature='"+ticket.getDepature()+"',destinction='"+ticket.getDestinction()+"',TrainNo='"+ticket.getTrainNo()+"',costTime='"+ticket.getCostTime()+"',TicketNo='"+ticket.getTicketNo()+"'  where TicketID='"+ticket.getTicketId()+"'";
		PreparedStatement pstmt = null;
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.executeUpdate();
			DBUtils.closeConnection(conn);
			System.out.println("修改车票成功");
			System.out.println(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public List<User> findAllUsers() throws SQLException {
		// TODO Auto-generated method stub
		List<User> users=new ArrayList<User>();
		Connection conn=null;
		conn=DBUtils.getConnection();
		String sql="select * from tab_user";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		User user=null;
		
		try {
			pstmt=conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			while(rs.next()) {
				// 解析结果集对象，封装查询结果
				//解析结果集对象，封装查询结果
				user=new User();
				
				user.setId(rs.getInt("ID"));
				user.setUsername(rs.getString("UserName"));
				user.setPassword(rs.getString("password"));
				user.setRule(rs.getString("rule"));
				user.setRealname(rs.getString("realname"));
				user.setSex(rs.getString("sex"));				
				user.setCity(rs.getString("city"));				
			   user.setCertType(rs.getString("cert_Type"));
			   user.setCert(rs.getString("cert"));
		
			users.add(user);
			}
			} finally {
				DBUtils.closeStatement(rs, pstmt);
			}
			
			
			return users;	
	
}

}

